package com.weixing.mall.provider.service.impl;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.weixing.mall.base.constant.DatePattern;
import com.weixing.mall.base.constant.GlobalConstant;
import com.weixing.mall.core.enums.OrderSysTagEnum;
import com.weixing.mall.provider.api.model.enums.AgentLevelEnum;
import com.weixing.mall.provider.api.model.enums.OrderTypeEnum;
import com.weixing.mall.provider.mapper.DayReportCustomerMapper;
import com.weixing.mall.provider.mapper.DayReportMarketMapper;
import com.weixing.mall.provider.model.domain.Order;
import com.weixing.mall.provider.model.enums.AfterSaleResultEnum;
import com.weixing.mall.provider.model.enums.AfterSalesTypeEnum;
import com.weixing.mall.provider.model.enums.OrderFromEnum;
import com.weixing.mall.provider.model.enums.OrderLogTypeEnum;
import com.weixing.mall.provider.model.vo.report.customer.CustomerReportDetailVo;
import com.weixing.mall.provider.model.vo.report.customer.CustomerSeckillBonusDetailVo;
import com.weixing.mall.provider.model.vo.report.customer.CustomerSeckillBonusOneVo;
import com.weixing.mall.provider.model.vo.report.customer.CustomerSeckillBonusTwoVo;
import com.weixing.mall.provider.model.vo.report.market.*;
import com.weixing.mall.provider.service.IDayReportMarketService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author jzy
 * @date 2020/5/30
 */
@Service
public class DayReportMarketServiceImpl extends ServiceImpl<DayReportMarketMapper, Order> implements IDayReportMarketService {
    @Autowired
    private DayReportCustomerMapper dayReportCustomerMapper;

    @Override
    public IPage<MarketSalesStatusVo> marketSalesStatus(Integer roleType, Long agentId, String startTime, String endTime, Integer orderType, Integer year, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="date_format(log.created_time,'%Y-%m-%d') date,agent.agent_name";
            q.eq("agent.role_type",1);
        }else{
            if (roleType==1){
                select="agent.kefu_id agentId,1 as roleType,date_format(log.created_time,'%Y-%m-%d') date";
            }
            if (roleType==2){
                select="agent.agent_id agentId,2 as roleType,date_format(log.created_time,'%Y-%m-%d') date";
            }
            q.eq("agent.role_type",roleType);
            q.eq("agent.agent_id",agentId);
        }
        q.select(select+
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) quantity"+
                ",count(if(log.log_type="+ OrderLogTypeEnum.REFUND.getValue() +",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+ OrderLogTypeEnum.CANCEL.getValue() +",true,null)) cancelQuantity" +
                ",count(if(log.log_type="+ OrderLogTypeEnum.STOCK_OUT.getValue() +",true,null)) stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) amount"+
                ",sum(if(log.log_type="+ OrderLogTypeEnum.REFUND.getValue() +",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+ OrderLogTypeEnum.CANCEL.getValue() +",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+ OrderLogTypeEnum.STOCK_OUT.getValue() +",o.order_price,0)) as stockoutAmount" +
                "");
        q.eq(year!=null,"YEAR(log.created_time)",year);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        //只筛选审核完成的订单
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        // 排除分红订单只统计个人和团队订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        //默认时间正序
        q.orderByAsc(StrUtil.isEmpty(sortField),"log.created_time");
        q.groupBy("date");
        IPage iPage = baseMapper.marketSalesStatus(initMpPage, q);
        return iPage;
    }

    @Override
    public IPage<CustomerReportDetailVo> marketSalesStatusDetail(Integer isOwn, Integer roleType, Long agentId, Long agentPid, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId, String sortField, String sortType,String date, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName,o.agent_id agentId,o.agent_name agentName,o.sys_tag,childAgent.agent_discount childDiscount" +
                ",o.distributor_id distributorId,o.distributor_name distributorName,o.act_name" +
                ",o.order_status,o.buyer,o.order_sn,o.out_trade_no,o.order_type,agent.agent_discount discount,agent.agent_sn,o.shipper_name,log.operator_name confirmName,o.created_time,o.audit_time" +
                ",goods.goods_id,goods.product_id,goods.goods_img,goods.goods_name,goods.goods_sn,goods.commodity_code" +
                ",goods.style_no,goods.cat_id,goods.cat_name,goods.color,goods.size,goods.partition_name,goods.sku_desp,goods.price,goods.market_price" +
                ",goods.cost_price,goods.height,goods.weight,goods.quantity,goods.product_status" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone" +
                ",afterSale.after_sale_type,afterSale.after_sale_result,afterSale.status afterSaleStatus,afterSale.customer_remark,max(log.created_time) auditedTime" +
                ",if("+roleType+"=2,agent.agent_discount,1) auditorDiscount" +
                //",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount) discountPrice" +
                ",if(o.order_type=3,o.order_price,if(agent.role_type=2,o.order_price*agent.agent_discount,agent.need_receive)) as discountPrice" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+" and agent.receivor_role=2,agent.need_uppay,0) refundHigherAmount" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",agent.need_receive,0) refundLowerAmount" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",agent.payer,null) refundObjectName" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",agent.payer_id,null) refundObjectId" +
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                "");
        //如果isOwn===1，就只查自己的订单,否则就查自己和团队的
        if (isOwn!=null&&isOwn.equals(1)){
            q.eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN);
        }
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.and(roleType!=null,i->i.eq("agent.agent_id",agentId).eq("agent.role_type",roleType).eq(agentPid!=null,"agent.agent_pid",agentPid));
        q.eq(roleType==null,"agent.role_type",1);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        q.groupBy("o.id");
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
                //没有这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
            q.le(StrUtil.isNotEmpty(date),"date_format(afterSale.audited_time,'%Y-%m-%d')",date);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
            q.eq(StrUtil.isNotEmpty(date),"date_format(log.created_time,'%Y-%m-%d')",date);
        }
        //订单操作时间
        q.isNotNull("log.created_time");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerReportDetailVo> dayReportDetail = dayReportCustomerMapper.getDayReportDetail(initMpPage, q);
        return dayReportDetail;
    }

    @Override
    public Map<String, Object> marketSalesStatusTotal(Integer isOwn, Integer roleType, Long agentId, Long agentPid, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId,String date) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("sum(o.order_price) as saleAmount" +
                ",sum(if(o.order_type=3,o.order_price,if(agent.role_type=2,o.order_price*agent.agent_discount,agent.need_receive))) as actualTurnover" +
                "");
        //如果isOwn===1，就只查自己的订单,否则就查自己和团队的
        if (isOwn!=null&&isOwn.equals(1)){
            q.eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN);
        }
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.and(roleType!=null,i->i.eq("agent.agent_id",agentId).eq("agent.role_type",roleType).eq(agentPid!=null,"agent.agent_pid",agentPid));
        q.eq(roleType==null,"agent.role_type",1);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
                //没有这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
            q.le(StrUtil.isNotEmpty(date),"date_format(afterSale.audited_time,'%Y-%m-%d')",date);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
            q.eq(StrUtil.isNotEmpty(date),"date_format(log.created_time,'%Y-%m-%d')",date);
        }
        //订单操作时间
        q.isNotNull("log.created_time");
        Map<String, Object> map = dayReportCustomerMapper.getOrderMap(q);
        return map;
    }

    @Override
    public IPage<MarketAgentDataAnalysisVo> marketAgentData(Integer roleType, Long agentId, String startTime, String endTime, Integer year, String sortField, String sortType, Page initMpPage) {
       // TODO REPORT 代理关系数据可能统计的不对。
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="date_format(o.audit_time,'%Y-%m') date,o.customer_id,o.order_type,o.customer_name,agent.agent_id,agent.agent_name,2 as roleType,case agent.agent_level when "+ AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        //广告查询条件
        String adsCondation="1=1";
        //订单子查询条件
        String orderCondation="1=1";
        //退款子查询条件
        String refundCondation="1=1";
        //会员子查询条件
        String memberCondation="1=1";
        if (StrUtil.isNotEmpty(startTime)){
            adsCondation=adsCondation+" and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"'";
            orderCondation=orderCondation+" and date_format(o.audit_time,'%Y-%m-%d')>='"+startTime+"'";
            refundCondation=refundCondation+" and date_format(refund.refund_time,'%Y-%m-%d')>='"+startTime+"'";
            memberCondation=memberCondation+" and date_format(member.register_time,'%Y-%m-%d')>='"+startTime+"'";
        }
        if (StrUtil.isNotEmpty(endTime)){
            adsCondation=adsCondation+" and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'";
            orderCondation=orderCondation+" and date_format(o.audit_time,'%Y-%m-%d')<='"+endTime+"'";
            refundCondation=refundCondation+" and date_format(refund.refund_time,'%Y-%m-%d')<='"+endTime+"'";
            memberCondation=memberCondation+" and date_format(member.register_time,'%Y-%m-%d')<='"+startTime+"'";
        }
        if (year!=null){
            adsCondation=adsCondation+" and YEAR(record.pay_time)="+year;
            orderCondation=orderCondation+" and YEAR(o.audit_time)="+year;
            refundCondation=refundCondation+" and YEAR(refund.refund_time)="+year;
            memberCondation=memberCondation+" and YEAR(member.register_time)="+year;
        }
        q.select(select +
                ",count(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_typ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_typ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amountTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_typ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",count(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_typ="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_typ="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_typ="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_typ="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_typ="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_typ="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmountTeam" +

                //个人退货率（件数）
                ",IFNULL(count(if(agent.belong_type=1 and log.log_typ="+ OrderLogTypeEnum.REFUND.getValue() +",true,null))/count(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)),0) as refundRate" +
                //团队退货率（件数）
                ",IFNULL(count(if(log.log_typ="+ OrderLogTypeEnum.REFUND.getValue() +",true,null))/count(if(log.log_typ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)),0) as refundRateTeam" +

                //个人退货率（金额）
                ",IFNULL(sum(if(agent.belong_type=1 and log.log_typ="+ OrderLogTypeEnum.REFUND.getValue() +",o.order_price,0))/sum(if(agent.belong_type=1 and log.log_typ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)),0) as refundAmountRate" +
                //团队退货率（金额）
                ",IFNULL(sum(if(log.log_typ="+ OrderLogTypeEnum.REFUND.getValue() +",o.order_price,0))/sum(if(log.log_typ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)),0) as refundAmountRateTeam" +


                //自己广告数量
                ",(select count(0) from ads_media_advertsing ads left join ads_media_orderd_record record on record.id = ads.business_id where agent.agent_id=record.agent_id and "+adsCondation+") as adsQuantity " +
                //团队广告数量
                ",(select count(0) from ads_media_advertsing ads left join ads_media_orderd_record record on record.id = ads.business_id where FIND_IN_SET(record.agent_id,getChildren(agent.agent_id)) and "+adsCondation+") as adsQuantityTeam " +
                //自己广告金额
                ",IFNULL((select sum(ads.price) from ads_media_advertsing ads left join ads_media_orderd_record record on record.id = ads.business_id where agent.agent_id=record.agent_id and "+adsCondation+"),0) as adsAmount " +
                //团队广告金额
                ",IFNULL((select sum(ads.price) from ads_media_advertsing ads left join ads_media_orderd_record record on record.id = ads.business_id where FIND_IN_SET(record.agent_id,getChildren(agent.agent_id)) and "+adsCondation+"),0) as adsAmountTeam " +

                //打广告人数
                ",(select count(0) from (select record.agent_id from ads_media_advertsing ads left join ads_media_orderd_record record on record.id = ads.business_id where  FIND_IN_SET(record.agent_id,getChildren(agent.agent_id)) and "+adsCondation+" group by record.agent_id) as temp) as adsPeopleQuantity " +
                //销售人数
                ",(select count(0) from (select agent2.agent_id from omc_order_agent agent2 where agent2.receivor_role=agent.receivor_role and agent2.receivor_id=agent.receivor_id and "+orderCondation+" and agent2.belong_type!=3 group by agent2.agent_id) as temp1) as salesPeople" +
               //代理总人数
                ",(select count(0) from (select agent2.agent_id from omc_order_agent agent2 where agent2.receivor_role=agent.receivor_role and agent2.receivor_id=agent.receivor_id and "+orderCondation+" group by agent2.agent_id) as temp2) as totalAgents" +
                //试用代理人数
                ",(select count(0) from amc_agent ag where ag.is_official=0 and FIND_IN_SET(ag.id,getChildren(agent.agent_id))) as probationerAgents" +
                //代理缴费人数
                ",(select count(0) from amc_agent ag where ag.maturity_time>now() and FIND_IN_SET(ag.id,getChildren(agent.agent_id))) as paidAgents" +
                //代理不续费人数
                ",(select count(0) from amc_agent ag where ag.maturity_time<now() and FIND_IN_SET(ag.id,getChildren(agent.agent_id))) as noRenewAgents" +

                //退费人数
                ",(select count(0) from amc_agent ag left join amc_agent_refund refund on ag.id=refund.agent_id where "+refundCondation+" and FIND_IN_SET(ag.id,getChildren(agent.agent_id))) as refundAgents" +
                //会员人数
                ",(select count(0) from uac_member member where  FIND_IN_SET(member.agent_id,getChildren(agent.agent_id)) and "+memberCondation+") as members" +
                //由会员变为代理人数
                ",(select count(0) from uac_member member where  FIND_IN_SET(member.agent_id,getChildren(agent.agent_id)) and "+memberCondation+" and member.is_agent=1) as memberToAgents" +

                //直接银级人数
                ",(select count(0) from (select oog.agent_id from omc_order_agent oog where oog.agent_pid=agent.agent_id and oog.agent_type=2 group by oog.agent_id) temp4)  as directSilvers" +
                //团队裂变出来的银级总数
                ",(select count(0) from (select oog.agent_id from omc_order_agent oog where FIND_IN_SET(oog.agent_id,getChildren(agent.agent_id)) and oog.agent_type=2 group by oog.agent_id) temp7) as totalSilvers" +
                //间接银级人数
                ",(select count(0) from (select oog.agent_id from omc_order_agent oog left join omc_order_agent oog1 on oog.agent_id=oog1.agent_pid left join omc_order_agent oog2 on oog1.agent_id=oog2.agent_pid where oog2.agent_pid=agent.agent_id and oog.agent_type=2 group by oog.agent_id) temp5)  as indirectSilvers" +
                //有利益挂钩银级人数
                ",(select count(0) from (select oog.agent_id from omc_order_agent oog left join omc_order_agent oog1 on oog.agent_id=oog1.agent_pid where oog1.agent_pid=agent.agent_id and oog.agent_type=2 group by oog.agent_id) temp6)  as benefitSilvers" +

                //晋升为一级代理人数（三角晋升链）
                ",(select count(0) from (select promote.agent_id from amc_agent_promote promote where FIND_IN_SET(promote.agent_id,getChildren(agent.agent_id)) and promote.level_before="+AgentLevelEnum.LEVEL_2.getValue()+" and promote.level_after="+AgentLevelEnum.LEVEL_1.getValue()+" group by promote.agent_id) temp8) as toFirstLevels" +

                //盈利
                ",sum(if(agent.profit_type!=3,agent.agent_profit,0)) as profit" +
                //正价银级返还款
                ",sum(if(agent.order_type=1 and agent.profit_type=3,agent.agent_profit,0)) as regularRebate" +
                //正价4折银级返还款
                ",sum(if(agent.order_type=2 and agent.profit_type=3,agent.agent_profit,0)) as agentRebate" +
                //秒杀2.5折银级返还款
                ",sum(if(agent.order_type=3 and agent.profit_type=3,agent.agent_profit,0)) as seckillRebate" +
                //营收利润
                ",IFNULL(sum(agent.agent_profit),0) as revenues" +
                "");
        q.and(roleType!=null&&roleType==1,i->i.eq("agent.agent_pid",0).eq("agent.kefu_id",agentId));
        q.and(roleType!=null&&roleType==2,i->i.eq("agent.agent_id",agentId).or().eq("agent.agent_pid",agentId));
        q.eq(year!=null,"YEAR(o.audit_time)",year);
        //只筛选审核完成的订单
        q.isNotNull("log.created_time");
        q.isNotNull("o.order_type");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(log.created_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime)));
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        //默认时间正序
        q.orderByAsc(StrUtil.isEmpty(sortField),"log.created_time");
        q.groupBy("date","agent.order_type","agent.agent_id");
        IPage<MarketAgentDataAnalysisVo> pageData = baseMapper.marketAgentData(initMpPage, q);
        if (pageData.getRecords().size()>0){
            List<MarketAgentDataAnalysisVo> records = pageData.getRecords();
            records.forEach(vo ->{
                if (vo.getSalesPeople()>0){
                    //人均销售数量
                    vo.setAvgSalesPeople(new BigDecimal(vo.getQuantityTeam().toString()).divide(new BigDecimal(vo.getSalesPeople().toString()), 2, BigDecimal.ROUND_HALF_UP));
                    //人均销售金额
                    vo.setAvgSalesAmount(new BigDecimal(vo.getAmountTeam().toString()).divide(new BigDecimal(vo.getSalesPeople().toString()), 2, BigDecimal.ROUND_HALF_UP));
                }
                if (vo.getRevenues()!=null){
                    //实际利润
                    vo.setActualRevenues(vo.getRevenues().subtract(vo.getAdsAmount()));
                }
            });
        }
        return pageData;
    }

    @Override
    public List<MarketAreaSalesVo> areaReport(String province, String city, String region, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select0="address.province,";
        if (StrUtil.isNotEmpty(province)){
            select0=select0+"address.city,";
            q.eq("address.province",province);
            if (StrUtil.isNotEmpty(city)){
                select0=select0+"address.region,";
                q.eq("address.city",city);
                q.groupBy("address.city,address.region");
                if (StrUtil.isNotEmpty(region)) {
                    q.eq("address.region",region);
                }
            }else {
                q.groupBy("address.city");
            }
        }else{
            q.groupBy("address.province");
        }
        q.select(select0+"count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) amount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) stockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) returnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) cancelAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.DISPATCH_DEPT.getValue()+",true,null)) dispatchDept" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STORE_DEPT.getValue()+",true,null)) storeDept" +
                ",count(if(log.log_type="+OrderLogTypeEnum.EXCHANGE.getValue()+",true,null)) exchange" +
                "");
        //订单操作时间
        q.isNotNull("log.created_time");
        q.isNotNull("o.order_type");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        // q.isNotNull("address.province");
        List<MarketAreaSalesVo> list = baseMapper.areaReport(q);
        //计算总下单量，总取消，。。。
        QueryWrapper<Order> q2 = new QueryWrapper<>();
        q2.select("count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) amount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) stockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) returnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) cancelAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.DISPATCH_DEPT.getValue()+",true,null)) dispatchDept" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STORE_DEPT.getValue()+",true,null)) storeDept" +
                ",count(if(log.log_type="+OrderLogTypeEnum.EXCHANGE.getValue()+",true,null)) exchange" +
                "");
        //订单操作时间
        q2.isNotNull("log.created_time");
        q.isNotNull("o.order_type");
        q2.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q2.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        Map<String, Object> orderMap = baseMapper.getOrderMap(q2);
        list.forEach(marketAreaSalesVo -> {
            BigDecimal amount = new BigDecimal(orderMap.get("amount").toString());
            BigDecimal stockoutAmount = new BigDecimal(orderMap.get("stockoutAmount").toString());
            BigDecimal returnAmount = new BigDecimal(orderMap.get("returnAmount").toString());
            BigDecimal cancelAmount = new BigDecimal(orderMap.get("cancelAmount").toString());
            if (marketAreaSalesVo.getQuantity()>0){
                marketAreaSalesVo.setOrderRate(new BigDecimal(marketAreaSalesVo.getQuantity()).divide(new BigDecimal(orderMap.get("quantity").toString()), 4, BigDecimal.ROUND_HALF_UP));
                marketAreaSalesVo.setOrderAmountRate(marketAreaSalesVo.getAmount().divide(amount, 4, BigDecimal.ROUND_HALF_UP));
            }else{
                marketAreaSalesVo.setOrderRate(new BigDecimal(0)).setOrderAmountRate(new BigDecimal(0));
            }
            if (marketAreaSalesVo.getStockoutQuantity()>0){
                marketAreaSalesVo.setStockoutRate(new BigDecimal(marketAreaSalesVo.getStockoutQuantity()).divide(new BigDecimal(orderMap.get("stockoutQuantity").toString()), 4, BigDecimal.ROUND_HALF_UP));
                marketAreaSalesVo.setStockoutAmountRate(marketAreaSalesVo.getStockoutAmount().divide(stockoutAmount, 4, BigDecimal.ROUND_HALF_UP));
            }else{
                marketAreaSalesVo.setStockoutRate(new BigDecimal(0)).setStockoutAmountRate(new BigDecimal(0));
            }
            if (marketAreaSalesVo.getReturnQuantity()>0){
                marketAreaSalesVo.setReturnRate(new BigDecimal(marketAreaSalesVo.getReturnQuantity()).divide(new BigDecimal(orderMap.get("returnQuantity").toString()), 4, BigDecimal.ROUND_HALF_UP));
                marketAreaSalesVo.setReturnAmountRate(marketAreaSalesVo.getReturnAmount().divide(returnAmount, 4, BigDecimal.ROUND_HALF_UP));
            }else{
                marketAreaSalesVo.setReturnRate(new BigDecimal(0)).setReturnAmountRate(new BigDecimal(0));
            }
            if (marketAreaSalesVo.getCancelQuantity()>0){
                marketAreaSalesVo.setCancelRate(new BigDecimal(marketAreaSalesVo.getCancelQuantity()).divide(new BigDecimal(orderMap.get("cancelQuantity").toString()), 4, BigDecimal.ROUND_HALF_UP));
                marketAreaSalesVo.setCancelAmountRate(marketAreaSalesVo.getCancelAmount().divide(cancelAmount, 4, BigDecimal.ROUND_HALF_UP));
            }else{
                marketAreaSalesVo.setCancelRate(new BigDecimal(0)).setCancelAmountRate(new BigDecimal(0));
            }
        });
        return list;
    }

    @Override
    public List<MarketAreaSalesVo> areaReport2(Integer queryType, String province, String city, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select0="address.province,";
        if (queryType!=null){
            if (queryType == 1){
                if (StrUtil.isNotEmpty(province)){
                    select0=select0+"address.city,";
                    q.eq("address.province",province);
                    q.groupBy("address.province,address.city");
                }else{
                    q.groupBy("address.province");
                }
            }
            if (queryType == 2){
                select0=select0+"address.city,";
                if (StrUtil.isNotEmpty(city)){
                    select0=select0+"address.region,";
                    q.eq("address.city",city);
                    q.groupBy("address.city,address.region");
                }else {
                    q.groupBy("address.city");
                }
            }
        }
        q.select(select0+"count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) amount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) stockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) returnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) cancelAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.DISPATCH_DEPT.getValue()+",true,null)) dispatchDept" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STORE_DEPT.getValue()+",true,null)) storeDept" +
                ",count(if(log.log_type="+OrderLogTypeEnum.EXCHANGE.getValue()+",true,null)) exchange" +
                "");
        //订单操作时间
        q.isNotNull("log.created_time");
        q.isNotNull("o.order_type");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        // q.isNotNull("address.province");
        List<MarketAreaSalesVo> list = baseMapper.areaReport(q);
        //计算总下单量，总取消，。。。
        QueryWrapper<Order> q2 = new QueryWrapper<>();
        q2.select("count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) amount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) stockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) returnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) cancelAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.DISPATCH_DEPT.getValue()+",true,null)) dispatchDept" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STORE_DEPT.getValue()+",true,null)) storeDept" +
                ",count(if(log.log_type="+OrderLogTypeEnum.EXCHANGE.getValue()+",true,null)) exchange" +
                "");
        //订单操作时间
        q2.isNotNull("log.created_time");
        q.isNotNull("o.order_type");
        q2.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q2.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        Map<String, Object> orderMap = baseMapper.getOrderMap(q2);
        list.forEach(marketAreaSalesVo -> {
            BigDecimal amount = new BigDecimal(orderMap.get("amount").toString());
            BigDecimal stockoutAmount = new BigDecimal(orderMap.get("stockoutAmount").toString());
            BigDecimal returnAmount = new BigDecimal(orderMap.get("returnAmount").toString());
            BigDecimal cancelAmount = new BigDecimal(orderMap.get("cancelAmount").toString());
            if (marketAreaSalesVo.getQuantity()>0){
                marketAreaSalesVo.setOrderRate(new BigDecimal(marketAreaSalesVo.getQuantity()).divide(new BigDecimal(orderMap.get("quantity").toString()), 4, BigDecimal.ROUND_HALF_UP));
                marketAreaSalesVo.setOrderAmountRate(marketAreaSalesVo.getAmount().divide(amount, 4, BigDecimal.ROUND_HALF_UP));
            }else{
                marketAreaSalesVo.setOrderRate(new BigDecimal(0)).setOrderAmountRate(new BigDecimal(0));
            }
            if (marketAreaSalesVo.getStockoutQuantity()>0){
                marketAreaSalesVo.setStockoutRate(new BigDecimal(marketAreaSalesVo.getStockoutQuantity()).divide(new BigDecimal(orderMap.get("stockoutQuantity").toString()), 4, BigDecimal.ROUND_HALF_UP));
                marketAreaSalesVo.setStockoutAmountRate(marketAreaSalesVo.getStockoutAmount().divide(stockoutAmount, 4, BigDecimal.ROUND_HALF_UP));
            }else{
                marketAreaSalesVo.setStockoutRate(new BigDecimal(0)).setStockoutAmountRate(new BigDecimal(0));
            }
            if (marketAreaSalesVo.getReturnQuantity()>0){
                marketAreaSalesVo.setReturnRate(new BigDecimal(marketAreaSalesVo.getReturnQuantity()).divide(new BigDecimal(orderMap.get("returnQuantity").toString()), 4, BigDecimal.ROUND_HALF_UP));
                marketAreaSalesVo.setReturnAmountRate(marketAreaSalesVo.getReturnAmount().divide(returnAmount, 4, BigDecimal.ROUND_HALF_UP));
            }else{
                marketAreaSalesVo.setReturnRate(new BigDecimal(0)).setReturnAmountRate(new BigDecimal(0));
            }
            if (marketAreaSalesVo.getCancelQuantity()>0){
                marketAreaSalesVo.setCancelRate(new BigDecimal(marketAreaSalesVo.getCancelQuantity()).divide(new BigDecimal(orderMap.get("cancelQuantity").toString()), 4, BigDecimal.ROUND_HALF_UP));
                marketAreaSalesVo.setCancelAmountRate(marketAreaSalesVo.getCancelAmount().divide(cancelAmount, 4, BigDecimal.ROUND_HALF_UP));
            }else{
                marketAreaSalesVo.setCancelRate(new BigDecimal(0)).setCancelAmountRate(new BigDecimal(0));
            }
        });
        return list;
    }

    @Override
    public IPage<MarketRegularActBonusVo> regularActBonus(Integer level, Integer roleType, Long agentId, String userName,Integer orderType,String actName,  String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="agent.kefu_id customerId,agent.kefu_name customerName,agent.kefu_id agentId,agent.kefu_name userName,1 as roleType,'客服' as userRole";
            //如果角色类型为空，查出父id为0的
            q.eq("agent.agent_pid",0);
            q.groupBy("agent.kefu_id");
            q.like(StrUtil.isNotEmpty(userName),"agent.kefu_name",userName);
        }else{
            select="agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId,agent.agent_name userName,2 as roleType" +
                    ",case agent.agent_level when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
            if (roleType==1){
                q.eq("agent.kefu_id",agentId);
                q.eq("agent.agent_pid",0);
            }
            if (roleType==2){
                q.eq("agent.agent_pid",agentId);
                q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
            }
            q.groupBy("agent.agent_id");
        }
        q.select(select+
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL1+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusOne" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL2+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusTwo" +
                ",sum(if(agent.belong_type=3,agent.agent_profit,0)) as sum" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //这里统计正价和代理商活动的分红，排除秒杀活动的
        q.ne("agent.order_type", OrderTypeEnum.SECKILL.getValue());
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        //取消、断货、和退货的不算
        q.ne("o.sys_tag",OrderSysTagEnum.YTHC_TAG_505.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.DHF_TAG_306.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.QXF_TAG_307.getValue());
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage pageData = baseMapper.regularActBonus(initMpPage, q);
        if (pageData.getRecords().size()>0){
            pageData.getRecords().add(0,regularActBonusSum(roleType,agentId,userName,startTime,endTime));
        }
        return pageData;
    }

    public MarketRegularActBonusVo regularActBonusSum(Integer roleType, Long agentId, String userName, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="'合计'userRole";
            //如果角色类型为空，查出父id为0的
            q.eq("agent.agent_pid",0);
            q.like(StrUtil.isNotEmpty(userName),"agent.kefu_name",userName);
        }else{
            if (roleType==1){
                select="agent.kefu_id customerId,agent.kefu_name customerName,agent.kefu_id agentId,agent.kefu_name userName,1 as roleType" +
                        ",'客服'userRole";
                q.eq("agent.kefu_id",agentId);
                q.eq("agent.agent_pid",0);
            }
            if (roleType==2){
                select="o.customer_id customerId,o.customer_name customerName,agent.agent_pid agentId,agent.agent_pname userName,2 as roleType" +
                        ",case agent.agent_level when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                        "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                        "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                        "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                        "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                        "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
                q.eq("agent.agent_pid",agentId);
            }
            q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        }
        q.select(select+
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL1+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusOne" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL2+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusTwo" +
                ",sum(if(agent.belong_type=3,agent.agent_profit,0)) as sum" +
                "");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.isNotNull("o.order_type");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //取消、断货、和退货的不算
        q.ne("o.sys_tag",OrderSysTagEnum.YTHC_TAG_505.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.DHF_TAG_306.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.QXF_TAG_307.getValue());
        //这里统计正价和代理商活动的分红，排除秒杀活动的
        q.ne("agent.order_type", OrderTypeEnum.SECKILL.getValue());
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        return baseMapper.regularActBonusSum( q);
    }

    @Override
    public IPage<CustomerSeckillBonusDetailVo> regularActBonusDetail(Integer type,Integer orderType,String actName,Integer roleType, Long agentId, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("goods.product_id,goods.goods_id,goods.goods_img,goods.goods_name,goods.sku_desp,goods.color,goods.size,goods.quantity" +
                ",goods.goods_sn,goods.commodity_code,goods.style_no,goods.cat_id,goods.cat_name,goods.market_price,goods.price,goods.cost_price" +
                ",goods.product_status,goods.height,goods.weight,pay.out_trade_no,agent.agent_profit amount,agent.profit_ratio ratio,o.id,o.order_sn" +
                ",o.order_price,o.audit_time,o.audit_status,o.buyer,o.agent_name,o.agent_sn,o.discount,o.agent_cliam_line,o.distributor_name,o.customer_name" +
                ",o.order_from,o.order_status" +
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone");
        //一级分红
        if (type!=null&&type.equals(1)){
            q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL1);
        }
        //二级分红
        if (type!=null&&type.equals(2)){
            q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL2);
        }
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        if (roleType!=null&&roleType==1){
            q.eq("agent.kefu_id",agentId);
            q.eq("agent.agent_pid",0);
        }
        if (roleType!=null&&roleType==2){
            q.eq("agent.agent_id",agentId);
        }


        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);

        //这里统计正价和代理商活动的分红，排除秒杀活动的
        q.ne("agent.order_type", OrderTypeEnum.SECKILL.getValue());
        //取消、断货、和退货的不算
        q.ne("o.sys_tag",OrderSysTagEnum.YTHC_TAG_505.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.DHF_TAG_306.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.QXF_TAG_307.getValue());
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return  baseMapper.regularActBonusDetail(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillBonusOneVo> regularActBonusOne(Integer roleType,Long agentId, String userName,Integer orderType,String actName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId1,agent.agent_name agentName1" +
                ",team.agent_id agentId,team.agent_name agentName,agent.agent_profit bonusOne ");
        if (roleType!=null&&roleType==1){
            q.eq("agent.kefu_id",agentId);
            q.eq("agent.agent_pid",0);
        }
        if (roleType!=null&&roleType==2){
            q.eq("agent.agent_id",agentId);
        }
        q.isNotNull("o.order_type");
        q.eq(orderType!=null,"o.order_type",orderType);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL1);
        //这里统计正价和代理商活动的分红，排除秒杀活动的
        q.ne("agent.order_type", OrderTypeEnum.SECKILL.getValue());
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //取消、断货、和退货的不算
        q.ne("o.sys_tag",OrderSysTagEnum.YTHC_TAG_505.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.DHF_TAG_306.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.QXF_TAG_307.getValue());
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return baseMapper.regularActBonusOne(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillBonusTwoVo> regularActBonusTwo(Integer roleType,Long agentId, String userName1, String userName, Integer orderType,String actName,String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId2,agent.agent_name agentName2" +
                ",team.obligee_id1st agentId1,team.obligee1st agentName1,team.agent_id agentId,team.agent_name agentName,agent.agent_profit bonusTwo ");
        if (roleType!=null&&roleType==1){
            q.eq("agent.kefu_id",agentId);
            q.eq("agent.agent_pid",0);
        }
        if (roleType!=null&&roleType==2){
            q.eq("agent.agent_id",agentId);
        }

        q.isNotNull("o.order_type");
        q.eq(orderType!=null,"o.order_type",orderType);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);

        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL2);
        //这里统计正价和代理商活动的分红，排除秒杀活动的
        q.ne("agent.order_type", OrderTypeEnum.SECKILL.getValue());
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //取消、断货、和退货的不算
        q.ne("o.sys_tag",OrderSysTagEnum.YTHC_TAG_505.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.DHF_TAG_306.getValue());
        q.ne("o.sys_tag",OrderSysTagEnum.QXF_TAG_307.getValue());
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return baseMapper.regularActBonusTwo(initMpPage, q);
    }

    @Override
    public IPage<MarketRegularAgentVo> regularAndActivity(Integer roleType, Long agentId,Long agentPid, Integer pRole, Integer orderType, String startTime, String endTime,String actName, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="o.customer_id customerId,o.customer_name customerName,agent.kefu_id agentId,agent.kefu_name userName,1 as roleType,agent.agent_discount agentDiscount,'客服' as userRole";
            //如果角色类型为空，查出需收款人为客服的订单，并以收款人分组（客服）
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,2 as roleType,agent.agent_discount agentDiscount" +
                    ",case agent.agent_level when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                    "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            //如果是角色类型是代理商，就筛选 代理商自己的和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+",1,0) as isOwn" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0)) as amountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualPaidAmount" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as actualPaidAmount" +

                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualReturnAmount" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",agent.need_receive,0)) as actualReturnAmount" +

                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualStockoutAmount" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",agent.need_receive,0)) as actualStockoutAmount" +

                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualCancelAmount" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",agent.need_receive,0)) as actualCancelAmount" +

                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price)) as actualAmountMarket" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.price,-goods.price)) as actualSaleAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),if(agent.role_type=2,if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount),-agent.need_receive))) as actualSaleAmount" +

                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.order_from="+ OrderFromEnum.MEMBER.getValue() +",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.profit_type!=3,agent.agent_profit,0)) as rebate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");

        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //只筛选审核完成的订单
        q.isNotNull("log.created_time");
        q.isNotNull("o.order_type");
        //订单审核时间、订单取消时间、订单断货时间、订单退货时间
        q.and(StrUtil.isNotEmpty(startTime),
                i->i.and(j->j.ge("date_format(log.created_time,'%Y-%m-%d')",startTime).le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime)));
        q.eq(orderType!=null,"o.order_type",orderType);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage pageData = baseMapper.regularAndActivity(initMpPage, q);
        if (pageData.getRecords().size()>0){
            //合计的数据放在数组第一个
            pageData.getRecords().add(0,regularAndActivitySum(roleType,agentId,agentPid,pRole,orderType,startTime,endTime,actName));
        }
        return pageData;
    }
    public MarketRegularAgentVo regularAndActivitySum(Integer roleType, Long agentId,Long agentPid, Integer pRole, Integer orderType, String startTime, String endTime,String actName) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="'合计'as userRole";
            //如果角色类型为空，角色类型为客服的订单
            q.eq("agent.role_type",1);
        }else{
            if (roleType==1){
                select="agent.agent_id agentId,agent.role_type roleType" +
                        ",'合计' userRole" +
                        "";
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
            }
            if (roleType==2){
                select="agent.agent_id,agent.role_type roleType" +
                        ",'合计' userRole" +
                        "";
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
            }
        }
        q.select(select+
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0)) as amountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualPaidAmount" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as actualPaidAmount" +

                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualReturnAmount" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",agent.need_receive,0)) as actualReturnAmount" +

                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualStockoutAmount" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",agent.need_receive,0)) as actualStockoutAmount" +

                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualCancelAmount" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",agent.need_receive,0)) as actualCancelAmount" +

                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price)) as actualAmountMarket" +
                //",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.price,-goods.price)) as actualSaleAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),if(agent.role_type=2,if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount),-agent.need_receive))) as actualSaleAmount" +

                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.order_from="+ OrderFromEnum.MEMBER.getValue() +",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.profit_type!=3,agent.agent_profit,0)) as rebate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.isNotNull("o.order_type");
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        return baseMapper.regularAndActivitySum(q);
    }
    @Override
    public IPage<MarketReferrerVo> referrerReport(Integer roleType, Long agentId,Long distributorId, Integer promoteStatus,String promoteTime, Integer orderType,Long actId, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        //添加订单操作表后修改
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="date_format(log.created_time,'%Y-%m-%d') date,o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type as roleType,agent.agent_discount agentDiscount" +
                ",case agent.agent_level when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                "";
        if (roleType==null){
            return initMpPage;
        }else{
            if (roleType==1){
                return initMpPage;
            }
        }
        //晋升前
        if (promoteStatus!=null&&promoteStatus==1){
            /*QueryWrapper<Order> q2 = new QueryWrapper<>();
            q2.select("bonusGroup.agent_id");
            q2.eq(agentId!=null,"bonusGroup.obligee_id",agentId);
            q2.groupBy("bonusGroup.agent_id");
            List<Long> ids = baseMapper.selectIdsFromAgentGroup(q2);
            if (ids!=null&&ids.size()>0){
                q.in("agent.agent_id",ids);
            }else{
                return initMpPage;
            }*/
            if (distributorId!=null) {
                q.eq(distributorId!=null,"agent.agent_id",distributorId);
                // 要小于晋升时间
                q.le(StrUtil.isNotEmpty(promoteTime),"log.created_time",promoteTime);
            } else {
                // 如果没有传合伙人id，要查出所有合伙人
                List<ReferrerAgentVo> referrerAgentVoList = referrerReportAgents(roleType, agentId, null);
                if (referrerAgentVoList!=null) {
                    q.and(i->{
                        for (ReferrerAgentVo referrerAgentVo : referrerAgentVoList) {
                            Long agentId1 = referrerAgentVo.getAgentId();
                            Date promoteTime1 = referrerAgentVo.getPromoteTime();
                            String dateFmt = DateUtil.format(promoteTime1, DatePattern.NORM_DATETIME_PATTERN);
                            i.or(j->j.eq("agent.agent_id",agentId1).le("log.created_time",dateFmt));
                        }
                    });
                } else {
                    return initMpPage;
                }
            }
            q.isNull("bonus.bonus_team_id");
            q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id","agent.role_type");
        }
        //晋升后
        if (promoteStatus!=null&&promoteStatus==2){
            select=select+
                    ",bonus.payer_id issuerId" +
                    ",bonus.payer_role issuerRole" +
                    ",if(bonus.payer_role=1,kefu.real_name,obligor.agent_name) issuerName" +
                    ",if(bonus.payer_role=1,0,obligor.agent_level) issuerLevel" +
                    ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",bonus.agent_profit,-bonus.agent_profit)) rebate" +
                    "";
            q.eq(agentId!=null,"bonus.agent_id",agentId);
            q.eq(roleType!=null,"bonus.role_type",roleType);
            q.eq(distributorId!=null,"agent.agent_id",distributorId);
            q.isNotNull("bonus.bonus_team_id");
            q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id","agent.role_type","issuerId");
        }
        q.eq("agent.role_type",2);
        q.select(select +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualAmount" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" ,o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualCancelAmount" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(agent.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit"+
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive)) as shouldReceipt" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(agent.order_type=3,-o.order_price,-o.order_price*agent.agent_discount))) as actualTurnover" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.isNotNull("o.order_type");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(actId!=null,"o.act_id",actId);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage pageData = baseMapper.referrerReport(initMpPage, q,agentId);
        if (pageData.getRecords().size()>0){
            //合计的数据放在数组第一个
            pageData.getRecords().add(0,referrerReportSum(roleType,agentId,distributorId,promoteStatus,promoteTime,orderType,actId,startTime,endTime));
        }

        return pageData;
    }

    public MarketReferrerVo referrerReportSum(Integer roleType, Long agentId,Long distributorId, Integer promoteStatus,String promoteTime, Integer orderType, Long actId, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'合计' userRole" +
                "";
        if (roleType==null){
            return null;
        }else{
            if (roleType==1){
                return null;
            }
        }
        //晋升前
        if (promoteStatus!=null&&promoteStatus==1){
            /*QueryWrapper<Order> q2 = new QueryWrapper<>();
            q2.select("bonusGroup.agent_id");
            q2.eq(agentId!=null,"bonusGroup.obligee_id",agentId);
            q2.groupBy("bonusGroup.agent_id");
            List<Long> ids = baseMapper.selectIdsFromAgentGroup(q2);
            if (ids!=null&&ids.size()>0){
                q.in("agent.agent_id",ids);
            }else{
                return null;
            }*/
            if (distributorId!=null) {
                q.eq(distributorId!=null,"agent.agent_id",distributorId);
                // 要小于晋升时间
                q.le(StrUtil.isNotEmpty(promoteTime),"log.created_time",promoteTime);
            } else {
                // 如果没有传合伙人id，要查出所有合伙人
                List<ReferrerAgentVo> referrerAgentVoList = referrerReportAgents(roleType, agentId, null);
                if (referrerAgentVoList!=null) {
                    q.and(i->{
                        for (ReferrerAgentVo referrerAgentVo : referrerAgentVoList) {
                            Long agentId1 = referrerAgentVo.getAgentId();
                            Date promoteTime1 = referrerAgentVo.getPromoteTime();
                            String dateFmt = DateUtil.format(promoteTime1, DatePattern.NORM_DATETIME_PATTERN);
                            i.or(j->j.eq("agent.agent_id",agentId1).le("log.created_time",dateFmt));
                        }
                    });
                } else {
                    return null;
                }
            }
            q.isNull("bonus.bonus_team_id");
        }
        //晋升后
        if (promoteStatus!=null&&promoteStatus==2){
            q.eq(agentId!=null,"bonus.agent_id",agentId);
            q.eq(roleType!=null,"bonus.role_type",roleType);
            q.eq(distributorId!=null,"agent.agent_id",distributorId);
            q.isNotNull("bonus.bonus_team_id");
        }
        q.eq("agent.role_type",2);
        q.eq(distributorId!=null,"agent.agent_id",distributorId);
        q.select(select +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualAmount" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" ,o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",bonus.agent_profit,-bonus.agent_profit)) rebate" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualCancelAmount" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(agent.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit"+
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive)) as shouldReceipt" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(agent.order_type=3,-o.order_price,-o.order_price*agent.agent_discount))) as actualTurnover" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        MarketReferrerVo marketReferrerVo = baseMapper.referrerReportSum(q,agentId);
        return marketReferrerVo;
    }

    @Override
    public List<ReferrerAgentVo> referrerReportAgents(Integer roleType,Long agentId,Integer type) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("bonusGroup.agent_id,bonusGroup.agent_name,bonusGroup.agent_level,bonusGroup.promoteTime");
        q.eq(agentId!=null,"bonusGroup.obligee_id",agentId);
        q.eq(type!=null,"bonusGroup.type",type);
        if (roleType==null|| roleType==1){
            return new ArrayList<ReferrerAgentVo>();
        }
        q.groupBy("bonusGroup.agent_id");
        List<ReferrerAgentVo> agentVos = baseMapper.selectOrderAgentIdsByDistributorId(q);
        return agentVos;
    }

    @Override
    public List<ReferrerAgentVo> referrerReportAgentsSend(Integer roleType, Long agentId, Integer type,Long bonusId) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("bonusGroup.agent_id,bonusGroup.agent_name,bonusGroup.agent_level,bonusGroup.promoteTime");
        q.eq(agentId!=null,"bonusGroup.obligor",agentId);
        q.eq(roleType!=null,"bonusGroup.obligor_role",roleType);
        q.eq(bonusId!=null,"bonusGroup.obligee_id",bonusId);
        q.eq(type!=null,"bonusGroup.type",type);
        if (roleType==null){
            return new ArrayList<ReferrerAgentVo>();
        }
        q.groupBy("bonusGroup.agent_id");
        List<ReferrerAgentVo> agentVos = baseMapper.selectOrderAgentIdsByDistributorId(q);
        return agentVos;
    }

    @Override
    public List<ReferrerAgentVo> referrerReportBonusAgent(Integer roleType, Long agentId, Integer type,Long partnerId) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("bonusGroup.obligee agentName" +
                ",bonusGroup.obligee_id agentId" +
                ",bonusGroup.obligee_level agentLevel" +
                "");
        q.eq(agentId!=null,"bonusGroup.obligor",agentId);
        q.eq(roleType!=null,"bonusGroup.obligor_role",roleType);
        q.eq(partnerId!=null,"bonusGroup.agent_id",partnerId);
        q.eq(type!=null,"bonusGroup.type",type);
        if (roleType==null){
            return new ArrayList<ReferrerAgentVo>();
        }
        q.groupBy("bonusGroup.obligee_id");
        List<ReferrerAgentVo> agentVos = baseMapper.selectOrderAgentIdsByDistributorId(q);
        return agentVos;
    }

    @Override
    public IPage<CustomerReportDetailVo> referrerReportDetail(Integer isOwn, Integer roleType, Long agentId,Integer agentType, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId, String sortField, String sortType,Long distributorId,Long issuerId,Integer  issuerRole, Integer promoteStatus, String promoteTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName,o.agent_id agentId,o.agent_name agentName,o.sys_tag" +
                ",o.distributor_id distributorId,o.distributor_name distributorName" +
                ",o.order_status,o.buyer,o.order_sn,o.out_trade_no,o.order_type,o.discount,o.agent_sn,o.shipper_name,o.customer_name confirmName,o.created_time,o.audit_time" +
                ",goods.goods_id,goods.product_id,goods.goods_img,goods.goods_name,goods.goods_sn,goods.commodity_code" +
                ",goods.style_no,goods.cat_id,goods.cat_name,goods.color,goods.size,goods.partition_name,goods.sku_desp,goods.price,goods.market_price" +
                ",goods.cost_price,goods.height,goods.weight,goods.quantity,goods.product_status" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone" +
                ",afterSale.after_sale_type,afterSale.after_sale_result,afterSale.status afterSaleStatus,afterSale.customer_remark,afterSale.audited_time auditedTime" +
                ",agent.agent_discount auditorDiscount" +
                ",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount) discountPrice" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+" and agent.receivor_role=2,agent.need_uppay,0) refundHigherAmount" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",agent.need_receive,0) refundLowerAmount" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",agent.payer,null) refundObjectName" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",agent.payer_id,null) refundObjectId" +
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                "");
        if (roleType==null){
            return initMpPage;
        }else{
            if (roleType==1){
                return initMpPage;
            }
        }
        //晋升前
        if (promoteStatus!=null&&promoteStatus==1){
            /*QueryWrapper<Order> q2 = new QueryWrapper<>();
            q2.select("bonusGroup.agent_id");
            q2.eq(agentId!=null,"bonusGroup.obligee_id",agentId);
            q2.groupBy("bonusGroup.agent_id");
            List<Long> ids = baseMapper.selectIdsFromAgentGroup(q2);
            if (ids!=null&&ids.size()>0){
                q.in("agent.agent_id",ids);
            }else{
                return initMpPage;
            }*/
            if (distributorId!=null) {
                q.eq(distributorId!=null,"agent.agent_id",distributorId);
                // 要小于晋升时间
                q.le(StrUtil.isNotEmpty(promoteTime),"log.created_time",promoteTime);
            } else {
                // 如果没有传合伙人id，要查出所有合伙人
                List<ReferrerAgentVo> referrerAgentVoList = referrerReportAgents(roleType, agentId, null);
                if (referrerAgentVoList!=null) {
                    q.and(i->{
                        for (ReferrerAgentVo referrerAgentVo : referrerAgentVoList) {
                            Long agentId1 = referrerAgentVo.getAgentId();
                            Date promoteTime1 = referrerAgentVo.getPromoteTime();
                            String dateFmt = DateUtil.format(promoteTime1, DatePattern.NORM_DATETIME_PATTERN);
                            i.or(j->j.eq("agent.agent_id",agentId1).le("log.created_time",dateFmt));
                        }
                    });
                } else {
                    return null;
                }
            }
            q.isNull("bonus.bonus_team_id");
        }
        //晋升后
        if (promoteStatus!=null&&promoteStatus==2){
            q.eq(issuerId!=null,"bonus.payer_id",issuerId);
            q.eq(issuerRole!=null,"bonus.payer_role",issuerRole);
            q.eq(agentId!=null,"bonus.agent_id",agentId);
            q.eq(distributorId!=null,"agent.agent_id",distributorId);
            q.isNotNull("bonus.bonus_team_id");
        }
        q.eq("agent.role_type",2);
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        //q.groupBy("o.id");
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
                //没有这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        }
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerReportDetailVo> dayReportDetail = baseMapper.getDayReportDetail(initMpPage, q,agentId);
        return dayReportDetail;
    }

    @Override
    public IPage<CustomerReportDetailVo> referrerReportSendDetail(Integer isOwn, Integer roleType, Long agentId, Integer agentType, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId, String sortField, String sortType, Long distributorId, Integer promoteStatus, String promoteTime, Long bonusId,Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName,o.agent_id agentId,o.agent_name agentName,o.sys_tag" +
                ",o.distributor_id distributorId,o.distributor_name distributorName" +
                ",o.order_status,o.buyer,o.order_sn,o.out_trade_no,o.order_type,o.discount,o.agent_sn,o.shipper_name,o.customer_name confirmName,o.created_time,o.audit_time" +
                ",goods.goods_id,goods.product_id,goods.goods_img,goods.goods_name,goods.goods_sn,goods.commodity_code" +
                ",goods.style_no,goods.cat_id,goods.cat_name,goods.color,goods.size,goods.partition_name,goods.sku_desp,goods.price,goods.market_price" +
                ",goods.cost_price,goods.height,goods.weight,goods.quantity,goods.product_status" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone" +
                ",afterSale.after_sale_type,afterSale.after_sale_result,afterSale.status afterSaleStatus,afterSale.customer_remark,afterSale.audited_time auditedTime" +
                ",agent.agent_discount auditorDiscount" +
                ",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount) discountPrice" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+" and agent.receivor_role=2,agent.need_uppay,0) refundHigherAmount" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",agent.need_receive,0) refundLowerAmount" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",agent.payer,null) refundObjectName" +
                ",if(afterSale.after_sale_type="+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",agent.payer_id,null) refundObjectId" +
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                "");
        if (roleType==null){

            return initMpPage;
        }
        //晋升前
        if (promoteStatus!=null&&promoteStatus==1){
           /* QueryWrapper<Order> q2 = new QueryWrapper<>();
            q2.select("bonusGroup.agent_id");
            q2.eq(agentId!=null,"bonusGroup.obligor",agentId);
            q2.eq(roleType!=null,"bonusGroup.obligor_role",roleType);
            q2.groupBy("bonusGroup.agent_id");
            List<Long> ids = baseMapper.selectIdsFromAgentGroup(q2);
            if (ids!=null&&ids.size()>0){
                q.in("agent.agent_id",ids);
            }else{
                return initMpPage;
            }*/
            if (distributorId!=null) {
                q.eq(distributorId!=null,"agent.agent_id",distributorId);
                // 要小于晋升时间
                q.le(StrUtil.isNotEmpty(promoteTime),"log.created_time",promoteTime);
            } else {
                // 如果没有传合伙人id，要查出所有合伙人
                List<ReferrerAgentVo> referrerAgentVoList = referrerReportAgents(roleType, agentId, null);
                if (referrerAgentVoList!=null) {
                    q.and(i->{
                        for (ReferrerAgentVo referrerAgentVo : referrerAgentVoList) {
                            Long agentId1 = referrerAgentVo.getAgentId();
                            Date promoteTime1 = referrerAgentVo.getPromoteTime();
                            String dateFmt = DateUtil.format(promoteTime1, DatePattern.NORM_DATETIME_PATTERN);
                            i.or(j->j.eq("agent.agent_id",agentId1).le("log.created_time",dateFmt));
                        }
                    });
                } else {
                    return null;
                }
            }
            q.isNull("bonus.bonus_team_id");
        }
        //晋升后
        if (promoteStatus!=null&&promoteStatus==2){
            q.eq(agentId!=null,"bonus.payer_id",agentId);
            q.eq(roleType!=null,"bonus.payer_role",roleType);
            q.eq(distributorId!=null,"agent.agent_id",distributorId);
            q.isNotNull("bonus.bonus_team_id");
        }
        q.eq("agent.role_type",2);
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.eq(bonusId!=null,"bonus.agent_id",bonusId);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        //q.groupBy("o.id");
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
                //没有这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        }
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerReportDetailVo> dayReportDetail = baseMapper.getDayReportDetailSend(initMpPage, q,agentId);
        return dayReportDetail;
    }

    @Override
    public Map<String, Object> referrerReportDetailTotal(Integer isOwn, Integer roleType, Long agentId, Integer agentType,String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId,Long distributorId,Long issuerId,Integer  issuerRole, Integer promoteStatus, String promoteTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("sum(o.order_price) as saleAmount,sum(if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive)) as actualTurnover");
        if (roleType==null){
            return null;
        }else{
            if (roleType==1){
                return null;
            }
        }
        //晋升前
        if (promoteStatus!=null&&promoteStatus==1){
            /*QueryWrapper<Order> q2 = new QueryWrapper<>();
            q2.select("bonusGroup.agent_id");
            q2.eq(agentId!=null,"bonusGroup.obligee_id",agentId);
            q2.groupBy("bonusGroup.agent_id");
            List<Long> ids = baseMapper.selectIdsFromAgentGroup(q2);
            if (ids!=null&&ids.size()>0){
                q.in("agent.agent_id",ids);
            }else{
                return null;
            }*/
            if (distributorId!=null) {
                q.eq(distributorId!=null,"agent.agent_id",distributorId);
                // 要小于晋升时间
                q.le(StrUtil.isNotEmpty(promoteTime),"log.created_time",promoteTime);
            } else {
                // 如果没有传合伙人id，要查出所有合伙人
                List<ReferrerAgentVo> referrerAgentVoList = referrerReportAgents(roleType, agentId, null);
                if (referrerAgentVoList!=null) {
                    q.and(i->{
                        for (ReferrerAgentVo referrerAgentVo : referrerAgentVoList) {
                            Long agentId1 = referrerAgentVo.getAgentId();
                            Date promoteTime1 = referrerAgentVo.getPromoteTime();
                            String dateFmt = DateUtil.format(promoteTime1, DatePattern.NORM_DATETIME_PATTERN);
                            i.or(j->j.eq("agent.agent_id",agentId1).le("log.created_time",dateFmt));
                        }
                    });
                } else {
                    return null;
                }
            }
            q.isNull("bonus.bonus_team_id");
        }
        //晋升后
        if (promoteStatus!=null&&promoteStatus==2){
            q.eq(issuerId!=null,"bonus.payer_id",issuerId);
            q.eq(issuerId!=null,"bonus.payer_id",issuerId);
            q.eq(agentId!=null,"bonus.agent_id",agentId);
            q.eq(distributorId!=null,"agent.agent_id",distributorId);
            q.isNotNull("bonus.bonus_team_id");
        }
        q.eq("agent.role_type",2);
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
                //没有这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        }
        Map<String, Object> map = baseMapper.getDayReportDetailTotal(q,agentId);
        return map;
    }

    @Override
    public Map<String, Object> referrerReportSendDetailTotal(Integer isOwn, Integer roleType, Long agentId, Integer agentType, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId, Long distributorId, Integer promoteStatus, String promoteTime,Long bonusId) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("sum(o.order_price) as saleAmount,sum(if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive)) as actualTurnover");
        if (roleType==null){
            return null;
        }
        //晋升前
        if (promoteStatus!=null&&promoteStatus==1){
            /*QueryWrapper<Order> q2 = new QueryWrapper<>();
            q2.select("bonusGroup.agent_id");
            q2.eq(agentId!=null,"bonusGroup.obligor",agentId);
            q2.eq(roleType!=null,"bonusGroup.obligor_role",roleType);
            q2.groupBy("bonusGroup.agent_id");
            List<Long> ids = baseMapper.selectIdsFromAgentGroup(q2);
            if (ids!=null&&ids.size()>0){
                q.in("agent.agent_id",ids);
            }else{
                return null;
            }*/
            if (distributorId!=null) {
                q.eq(distributorId!=null,"agent.agent_id",distributorId);
                // 要小于晋升时间
                q.le(StrUtil.isNotEmpty(promoteTime),"log.created_time",promoteTime);
            } else {
                // 如果没有传合伙人id，要查出所有合伙人
                List<ReferrerAgentVo> referrerAgentVoList = referrerReportAgents(roleType, agentId, null);
                if (referrerAgentVoList!=null) {
                    q.and(i->{
                        for (ReferrerAgentVo referrerAgentVo : referrerAgentVoList) {
                            Long agentId1 = referrerAgentVo.getAgentId();
                            Date promoteTime1 = referrerAgentVo.getPromoteTime();
                            String dateFmt = DateUtil.format(promoteTime1, DatePattern.NORM_DATETIME_PATTERN);
                            i.or(j->j.eq("agent.agent_id",agentId1).le("log.created_time",dateFmt));
                        }
                    });
                } else {
                    return null;
                }
            }
            q.isNull("bonus.bonus_team_id");
        }
        //晋升后
        if (promoteStatus!=null&&promoteStatus==2){
            q.eq(agentId!=null,"bonus.payer_id",agentId);
            q.eq(roleType!=null,"bonus.payer_role",roleType);
            q.eq(distributorId!=null,"agent.agent_id",distributorId);
            q.isNotNull("bonus.bonus_team_id");
        }
        q.eq(bonusId!=null,"bonus.agent_id",bonusId);
        q.eq("agent.role_type",2);
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
                //没有这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        }
        Map<String, Object> map = baseMapper.getDayReportDetailTotalSend(q,agentId);
        return map;
    }

    @Override
    public IPage<MarketReferrerSendVo> referrerReportSend(Integer roleType, Long agentId, Long distributorId,Long bonusId, Integer promoteStatus, String promoteTime, Integer orderType,Long actId, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="date_format(log.created_time,'%Y-%m-%d') date,o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type as roleType,agent.agent_discount agentDiscount" +
                ",case agent.agent_level when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                ",bonus.payer_id issuerId" +
                ",bonus.agent_id bonusId" +
                ",bonus.agent_name bonusName" +
                ",bonus.agent_level bonusLevel" +
                "";
        if (roleType==null){
            return initMpPage;
        }
        //晋升前
        if (promoteStatus!=null&&promoteStatus==1){
            /*QueryWrapper<Order> q2 = new QueryWrapper<>();
            q2.select("bonusGroup.agent_id");
            q2.eq(agentId!=null,"bonusGroup.obligor",agentId);
            q2.eq(roleType!=null,"bonusGroup.obligor_role",roleType);
            q2.groupBy("bonusGroup.agent_id");
            List<Long> ids = baseMapper.selectIdsFromAgentGroup(q2);
            if (ids!=null&&ids.size()>0){
                q.in("agent.agent_id",ids);
            }else{
                return initMpPage;
            }*/
            if (distributorId!=null) {
                q.eq(distributorId!=null,"agent.agent_id",distributorId);
                // 要小于晋升时间
                q.le(StrUtil.isNotEmpty(promoteTime),"log.created_time",promoteTime);
            } else {
                // 如果没有传合伙人id，要查出所有合伙人
                List<ReferrerAgentVo> referrerAgentVoList = referrerReportAgents(roleType, agentId, null);
                if (referrerAgentVoList!=null) {
                    q.and(i->{
                        for (ReferrerAgentVo referrerAgentVo : referrerAgentVoList) {
                            Long agentId1 = referrerAgentVo.getAgentId();
                            Date promoteTime1 = referrerAgentVo.getPromoteTime();
                            String dateFmt = DateUtil.format(promoteTime1, DatePattern.NORM_DATETIME_PATTERN);
                            i.or(j->j.eq("agent.agent_id",agentId1).le("log.created_time",dateFmt));
                        }
                    });
                } else {
                    return null;
                }
            }
            q.isNull("bonus.bonus_team_id");
            q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id","agent.role_type");
        }
        //晋升后
        if (promoteStatus!=null&&promoteStatus==2){
            select=select+
                    ",if(bonus.payer_role=1,kefu.real_name,obligor.agent_name) issuerName" +
                    ",if(bonus.payer_role=1,0,obligor.agent_level) issuerLevel" +
                    ",sum(if(bonus.profit_type="+GlobalConstant.PROFIT_TYPE_BONUS+",if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",bonus.agent_profit,-bonus.agent_profit),0)) as personRebate" +
                    ",sum(if(bonus.profit_type="+GlobalConstant.PROFIT_TYPE_BONUS+",if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",bonus.need_receive,-bonus.need_receive),0)) as rebate" +
                    ",sum(if(bonus.profit_type="+GlobalConstant.PROFIT_TYPE_BONUS+",if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",bonus.need_uppay,-bonus.need_uppay),0)) as lowerRebate" +
                    "" ;
            q.eq(agentId!=null,"bonus.payer_id",agentId);
            q.eq(roleType!=null,"bonus.payer_role",roleType);
            q.eq(distributorId!=null,"agent.agent_id",distributorId);
           q.isNotNull("bonus.bonus_team_id");
            q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id","agent.role_type","bonus.agent_id");
        }
        q.eq("agent.role_type",2);
        q.select(select +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualAmount" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" ,o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualCancelAmount" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(agent.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit"+
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive)) as shouldReceipt" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(agent.order_type=3,-o.order_price,-o.order_price*agent.agent_discount))) as actualTurnover" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.eq(bonusId!=null,"bonus.agent_id",bonusId);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        if(StrUtil.isEmpty(sortField)){
            q.orderByDesc("log.created_time");
        }
        IPage pageData = baseMapper.referrerReportSend(initMpPage, q,promoteStatus);
        if (pageData.getRecords().size()>0){
            //合计的数据放在数组第一个
            pageData.getRecords().add(0,referrerReportSendSum(roleType,agentId,distributorId,bonusId,promoteStatus,promoteTime,orderType,actId,startTime,endTime));
        }

        return pageData;
    }

    public MarketReferrerSendVo referrerReportSendSum(Integer roleType, Long agentId,Long distributorId,Long bonusId, Integer promoteStatus,String promoteTime, Integer orderType, Long actId, String startTime, String endTime) {

        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'合计' userRole" +
                "";
        if (roleType==null){
            return null;
        }
        //晋升前
        if (promoteStatus!=null&&promoteStatus==1){
            /*QueryWrapper<Order> q2 = new QueryWrapper<>();
            q2.select("bonusGroup.agent_id");
            q2.eq(agentId!=null,"bonusGroup.obligor",agentId);
            q2.eq(roleType!=null,"bonusGroup.obligor_role",roleType);
            q2.groupBy("bonusGroup.agent_id");
            List<Long> ids = baseMapper.selectIdsFromAgentGroup(q2);
            if (ids!=null&&ids.size()>0){
                q.in("agent.agent_id",ids);
            }else{
                return null;
            }*/
            if (distributorId!=null) {
                q.eq(distributorId!=null,"agent.agent_id",distributorId);
                // 要小于晋升时间
                q.le(StrUtil.isNotEmpty(promoteTime),"log.created_time",promoteTime);
            } else {
                // 如果没有传合伙人id，要查出所有合伙人
                List<ReferrerAgentVo> referrerAgentVoList = referrerReportAgents(roleType, agentId, null);
                if (referrerAgentVoList!=null) {
                    q.and(i->{
                        for (ReferrerAgentVo referrerAgentVo : referrerAgentVoList) {
                            Long agentId1 = referrerAgentVo.getAgentId();
                            Date promoteTime1 = referrerAgentVo.getPromoteTime();
                            String dateFmt = DateUtil.format(promoteTime1, DatePattern.NORM_DATETIME_PATTERN);
                            i.or(j->j.eq("agent.agent_id",agentId1).le("log.created_time",dateFmt));
                        }
                    });
                } else {
                    return null;
                }
            }
            q.isNull("bonus.bonus_team_id");
        }
        //晋升后
        if (promoteStatus!=null&&promoteStatus==2){
            select=select+
                    ",sum(if(bonus.profit_type="+GlobalConstant.PROFIT_TYPE_BONUS+",if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",bonus.agent_profit,-bonus.agent_profit),0)) as personRebate" +
                    ",sum(if(bonus.profit_type="+GlobalConstant.PROFIT_TYPE_BONUS+",if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",bonus.need_receive,-bonus.need_receive),0)) as rebate" +
                    ",sum(if(bonus.profit_type="+GlobalConstant.PROFIT_TYPE_BONUS+",if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",bonus.need_uppay,-bonus.need_uppay),0)) as lowerRebate";
            q.eq(agentId!=null,"bonus.payer_id",agentId);
            q.eq(roleType!=null,"bonus.payer_role",roleType);
            q.eq(distributorId!=null,"agent.agent_id",distributorId);
            q.isNotNull("bonus.bonus_team_id");
        }
        q.eq("agent.role_type",2);
        q.select(select +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualAmount" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" ,o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),0)) as actualCancelAmount" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(agent.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit"+
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive)) as shouldReceipt" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(agent.order_type=3,-o.order_price,-o.order_price*agent.agent_discount))) as actualTurnover" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.eq(bonusId!=null,"bonus.agent_id",bonusId);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        MarketReferrerSendVo marketReferrerVo = baseMapper.referrerReportSendSum(q,bonusId,agentId,roleType,promoteStatus);
        return marketReferrerVo;
    }
}
